package org.trajectory.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.pentaho.commons.connection.IPentahoConnection;
import org.pentaho.commons.connection.IPentahoResultSet;
import org.springframework.beans.factory.annotation.Autowired;
import org.trajectory.data.Line;
import org.trajectory.data.TrajectorySequence;
import org.trajectory.data.TrajectorySequence.TrajectorySequenceGroupByTime;
import org.trajectory.data.TrajectorySequence.TrajectorySequenceGroupByTime.TrajectorySequenceGroupByTimeChild;

public class TrajectoryLoader{
	public ConnectionService connectionService;

	@Autowired
	public void setConnectionService(ConnectionService connectionService) {
		this.connectionService = connectionService;
	}

	private IPentahoConnection getConnection(String datasource) {
		return this.connectionService.getConnection(datasource);
	}
	
	private List<TrajectorySequenceGroupByTime> trajectorySequenceGroupByTime = new ArrayList<TrajectorySequenceGroupByTime>();
	public void download(String datasource , long from , long to , String lines , int shop) throws Exception{
		trajectorySequenceGroupByTime.clear();
		String where = " where 1 = 1";
		if(from > 0)
			where += " and time >= '" + new Timestamp(from) + "'";
		if(to > 0){
			where += " and time <= '" + new Timestamp(to) + "'";
		}
		if(lines != null){
			where += " and line_seq in ( " + lines + ")";
		}
		IPentahoConnection conn = null;
		IPentahoResultSet rs = null;
		try{
			conn = getConnection(datasource);
			rs = conn
					.executeQuery("select id , sku , x , y , line_id , time , distance , reader , session_id , is_sold_time,line_seq from trajectory"+shop + where + " order by time , line_id;");
			Object[] obj;
			long cur = 0;
			long t = 0;
			while ((obj = rs.next()) != null) {
				t = ((Timestamp)obj[5]).getTime();
				TrajectorySequenceGroupByTime trajectory = null;
				List<TrajectorySequenceGroupByTimeChild> children = null;
				if(t > cur){//new Item
					cur = t;
					trajectory = new TrajectorySequence.TrajectorySequenceGroupByTime();
					children = new ArrayList<TrajectorySequenceGroupByTimeChild>();
					trajectory.setChildren(children);
					trajectorySequenceGroupByTime.add(trajectory);
				}else{
					trajectory = trajectorySequenceGroupByTime.get(trajectorySequenceGroupByTime.size() - 1);
					children = trajectory.getChildren();
				}
				trajectory.setTime(new Timestamp(t));
				TrajectorySequenceGroupByTimeChild child = new TrajectorySequenceGroupByTimeChild();
				child.setDistance(Integer.valueOf(obj[6].toString()));
				child.setLineId(Integer.valueOf(obj[4].toString()));
				child.setReaderId(obj[7].toString());
				child.setSessionId(obj[8].toString());
				child.setX(Integer.valueOf(obj[2].toString()));
				child.setY(Integer.valueOf(obj[3].toString()));
				child.setSku(obj[1].toString());
				child.setIsSoldTime(Integer.valueOf(obj[9].toString()));
				child.setLineSeq(obj[10].toString());
				children.add(child);
			}
		}finally{
			if(rs != null)
				rs.close();
			if(conn != null)
				conn.close();
		}
	}
	public List<TrajectorySequenceGroupByTime> getTrajectorySequence(){
		return trajectorySequenceGroupByTime;
	}
	public List<Line> loadAllLines(String datasource , int shop) throws Exception{
		List<Line> list = new ArrayList<Line>();
		IPentahoConnection conn = null;
		IPentahoResultSet rs = null;
		try{
			conn = getConnection(datasource);
			rs = conn.executeQuery("select line_seq , min(`time`),max(`time`) from trajectory"+shop+" group by line_seq order by line_seq;");
			Object[] obj;
			while ((obj = rs.next()) != null){
				Line line = new Line();
				line.setLineSeq(String.valueOf(obj[0]));
				line.setStart((Timestamp)obj[1]);
				line.setEnd((Timestamp)obj[2]);
				list.add(line);
			}
		}finally{
			if(rs != null)
				rs.close();
			if(conn != null)
				conn.close();
		}
		return list;
	}
}
